const db = require("../server/db.js");

module.exports = {
  async getBigProducts(limit, offset) {
    const connection = await db.getConnection();
    try {
      await connection.beginTransaction();
      limit = parseInt(limit) || 10;
      offset = parseInt(offset) || 0;
      // 使用问号占位符而不是命名参数
      let sql = "SELECT * FROM products LIMIT ? OFFSET ?";

      // 准备参数
      const [products] = await connection.query(sql, [
        limit, // 默认每页10条
        offset, // 默认从第0条开始
      ]);

      // 查询总记录数
      const [totalRows] = await connection.query(
        "SELECT COUNT(*) as total FROM products"
      );
      const total = totalRows[0].total;
      console.log(total);
      // 计算总页数
      const totalPages = Math.ceil(total / limit);

      return {
        products,
        totalPages,
      };
    } catch (error) {
      console.error("Search products error:", error);
      throw error;
    } finally {
      connection.release();
      console.log("数据库连接已释放");
    }
  },
  async searchProducts(params) {
    const connection = await db.getConnection();
    try {
      await connection.beginTransaction();
      // 使用问号占位符而不是命名参数
      let sql = `
      SELECT 
          product_id, 
          product_code, 
          store, 
          color, 
          spec, 
          name, 
          CAST(price AS DECIMAL(10,2)) AS price, 
          image, 
          stock, 
          description, 
          created_at, 
          updated_at, 
          status 
      FROM 
          products 
      WHERE 
          name LIKE ? 
          OR description LIKE ?
      LIMIT ? OFFSET ?`;

      // 准备参数
      const searchTerm = params.key ? `%${params.key}%` : "%";
      const [products] = await connection.query(sql, [
        searchTerm, // name LIKE '%keyword%'
        searchTerm, // description LIKE '%keyword%'
        params.limit || 10, // 默认每页10条
        params.offset || 0, // 默认从第0条开始
      ]);

      return products;
    } catch (error) {
      console.error("Search products error:", error);
      throw error;
    } finally {
      connection.release();
      console.log("数据库连接已释放");
    }
  },
};
